# -*- coding: UTF-8 -*-
import datetime
from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column, Integer, String, ForeignKey, types
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select

import base64

from pylons import config

Session=sessionmaker(autoflush=True, autocommit=False)
metadata = MetaData()

types_mapper = {
                'integer': types.Integer,
                'string': types.String,
                'unicode': types.Unicode,
                'float': types.Float,
                'date': types.Date,
                'datetime': types.DateTime,
                'timestamp': types.TIMESTAMP,
                'numeric': types.Numeric,
                'boolean': types.Boolean,
                'text': types.TEXT,
                'binary': types.Binary
                }

class ExistingTableException(Exception): pass
class TableNotExistException(Exception): pass
class UnknownTypeException(Exception): pass
class IntegrityError(Exception): pass

class Database:
    def __init__(self):
        ### Connect to Database
        self.engine = create_engine(config.get('sqlalchemy.default.url'), echo=False)
        ### Create MetaData instance
        self.session = None
        self.metadata = metadata
        self.metadata.bind = self.engine
        self.tables={}
        self._loadTables()

    def _StartOperation(self):
        self.session = Session(bind=self.engine)
        self.session.bind.connect()
        self.session.begin(subtransactions=True)

    def _EndOperation(self):
        self.session.flush()
        self.session.close()
        self.session.clear()

    def createTable(self, tabledef, fields=None):
        name = tabledef['name']
        if not self.tables.has_key(name):
            ### Define Table on MetaData
            table = Table(name, self.metadata)
            for field in fields:
                if not types_mapper.has_key(field['type']):
                    raise UnknownTypeException(field['type'])
                kwargs = {}
                if field.get('primarykey') is not None:
                    kwargs['primary_key'] = True
                if field.get('autoincrement') is not None:
                    kwargs['autoincrement'] = True
                if field.get('default') is not None:
                    kwargs['default'] = field.get('default')
                if field.get('unique') is not None:
                    kwargs['unique'] = True
                if field.get('nullable') is not None:
                    kwargs['nullable'] = False
                if field.get('index') is not None:
                    kwargs['index'] = True
                column = Column(field['name'], types_mapper[field['type']], **kwargs)
                table.append_column(column)
            if not tabledef.has_key('created') or tabledef['created'] != 'false':
                table.append_column(Column("created", types.DateTime, default=datetime.datetime.now()))
            if not tabledef.has_key('updated') or tabledef['updated'] != 'false':
                table.append_column(Column("updated", types.DateTime, onupdate=datetime.datetime.now()))
            ### Save MetaData on Database
            self.metadata.reflect(bind=self.engine)
            table.create(bind=self.engine)#session.bind)#, checkfirst=True)

            #TODO: si tabla no pudo ser creada entonces debe ser borrada del metadata
            self.tables[name]=table
        else:
            raise ExistingTableException(name)

    def dropTable(self, name):
        if self.tables.has_key(name):
            self._StartOperation()
            ### Drop Table
            self.tables.get(name).drop(bind=self.session.bind)#, checkfirst=True)
            self.metadata.remove(self.tables[name])
            ### Delete table from dictionary
            del self.tables[name]
            self._EndOperation()

    def dropAllTables(self):
        self._StartOperation()
        ### Drop Table
        self.metadata.drop_all(bind=self.session.bind)
        self.metadata.clear()
        ### Delete table from dictionary
        self.tables = {}
        self._EndOperation()

    def _loadTables(self):
        self._StartOperation()
        self.metadata.bind = self.engine
        self.metadata.reflect()
        self.tables = dict([(t.name, t) for t in self.metadata.metadata.sorted_tables])
        self.metadata.reflect()
        self._EndOperation()

    def reloadTables(self):
        self._loadTables()

    def _countTables(self):
        self._loadTables()
        return len(self.tables)

    def getRecords(self, table):
        if self.tables.has_key(table):
            s = select([self.tables[table]])
            r = self.engine.execute(s)
            columns = self.getColumns(table)
            result = []
            for row in r:
                field = {}
                for col in columns:
                    field[col] = row[col]
                result.append(field)
            return result
        else:
            raise TableNotExistException(table)

    def insertRecord(self, table_name, fields):
        #fields["restrecord_id"]=None
        if self.tables.has_key(table_name):
            ins = self.tables[table_name].insert(values=fields)
            result = self.engine.execute(ins)
            return result.last_inserted_ids()[0]
        else:
            raise TableNotExistException(table)

    def getColumns(self, table_name):
        if self.tables.has_key(table_name):
            self._StartOperation()
            return [t.name for t in self.tables[table_name].columns]
            self._EndOperation()
        else:
            raise TableNotExistException(table_name)

    def getDefinition(self, table_name):
        if self.tables.has_key(table_name):
            return {'name': table_name, 'fields': dict([(field.name, self._getFieldDefinition(field)) for field in self.tables[table_name].columns])}
        else:
            raise TableNotExistException(table_name)

    def _getFieldDefinition(self, field):
        definition={
             "type":self._getFieldType(field),
             "data":field,
             "primarykey": field.primary_key,
             }

        if field.default:
            definition["default"]=field.default.arg

        return definition

    def _getFieldType(self, field):
        for typename, typeclass in types_mapper.iteritems():
            if isinstance(field.type, typeclass):
                return typename

    def decode_id(self, encoded_id):
        return base64.decodestring(encoded_id)

    def encode_id(self, decoded_id):
        return base64.encodestring(decoded_id)

    def get_encoded_id_fn(self, table_name):
        pk=self.get_table_pk(table_name)
        def get_encoded_id_from_record(record):
            return record.get(pk)
        return get_encoded_id_from_record

    def get_table_pk(self, table_name):
        pks=self.tables.get(table_name).primary_key.columns.keys()
        if pks:
            return pks[0]
        raise Exception("Missing Primary key on table '%s'" % table_name)

    def _get_table(self, table_name):
        if self.tables.has_key(table_name):
            return self.tables[table_name]
        else:
            raise TableNotExistException(table)

    def updateRecord(self, table_name, encoded_id, fields):
        table=self._get_table(table_name)
        data=dict(table_name=table_name,field_name=self.get_table_pk(table_name),value=self.decode_id(encoded_id))
        update = table.update("%(table_name)s.%(field_name)s = '%(value)s'" % data, values=fields)
        self.engine.execute(update)

    def deleteRecord(self, table_name, encoded_id):
        table=self._get_table(table_name)
        data=dict(table_name=table_name,field_name=self.get_table_pk(table_name),value=self.decode_id(encoded_id))
        delete = table.delete("%(table_name)s.%(field_name)s = '%(value)s'" % data)
        self.engine.execute(delete)

    def createRelation(self, table1, table2, type='manytomany'):
        if self.tables.has_key(table1) and self.tables.has_key(table2):
            table_name = '%s_%s' % (table1, table2)
            col2 = Column(table2 + '_id', Integer, ForeignKey(table2 + '.id'))
            if type == 'onetomany':
                col2.unique = True
            self.tables[table_name] = Table(table_name, metadata,
                    Column('id', Integer, primary_key=True, autoincrement=True),
                    Column(table1 + '_id', Integer, ForeignKey(table1 + '.id')),
                    col2
                    )
            self.tables[table_name].create(bind=self.engine)
        else:
            raise TableNotExistException(table1, table2)

    def insertRelationRecord(self, table1, table2, fields={}):
        table_name = '%s_%s' % (table1, table2)
        if self.tables.has_key(table_name):
            relation_table = self.tables[table_name]
            id_table1 = fields[table1 + '_id']
            id_table2 = fields[table2 + '_id']
            s1 = select([self.tables[table1].c.id == id_table1])
            if len(self.engine.execute(s1).fetchall()) <= 0:
                raise IntegrityError(id_table1)
            s2 = select([self.tables[table2].c.id == id_table2])
            if len(self.engine.execute(s2).fetchall()) <= 0:
                raise IntegrityError(id_table2)
            s = select([relation_table])
            result = self.engine.execute(s)
            for row in result:
                if row[table1 + '_id'] == fields[table1 + '_id'] and row[table2 + '_id'] == fields[table2 + '_id']:
                    return row['id']
            return self.insertRecord(table_name, fields)
        else:
            raise TableNotExistException(table1, table2)

#    def recordExists(self, table, id):
#        if self.tables.has_key(table):
#            ins = self.tables[table].insert(values=fields)
#            result = self.engine.execute(ins)
#        else:
#            raise TableNotExistException(table)